﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Data;

namespace DAL
{
    /// <summary>
    /// DAL to Receipt table
    /// </summary>
    public abstract class ReceiptDAL : BaseDAL, UTL.IBaseDAL
    {
        #region Contansts
        #endregion

        #region Objects
        #endregion

        #region Properties
        #endregion

        #region Constructors
        public ReceiptDAL() { }
        public ReceiptDAL(string connectString) : base(connectString) { }
        #endregion

        #region Implements
        /// <summary>
        /// Count all of records
        /// </summary>
        /// <returns>Number of records</returns>
        public int Count()
        {
            return _mdb.tb_Receipts.Count();
        }

        /// <summary>
        /// Select all data from table in database
        /// </summary>
        /// <returns>Data</returns>
        public DataTable Select()
        {
            try
            {
                var res = from s in _mdb.tb_Receipts
                          orderby s.Id descending
                          select new
                          {
                              s.Id,
                              s.LeaseId,
                              s.Date,
                              s.RoomFee,
                              s.ElectricFee,
                              s.WaterFee,
                              s.OtherFee,
                              s.Debt,
                              s.Note,
                              s.Status,

                              GuestName = s.tb_Lease.tb_Guest.Name,
                              IdCard = s.tb_Lease.tb_Guest.IdCard,
                              UserName = s.tb_Lease.tb_User.Name,

                              StatusName = s.tb_ReceiptStatus.Name,
                              SumFee = s.RoomFee + s.ElectricFee + s.WaterFee + s.OtherFee + s.Debt
                          };
                return res.ToDataTable();
            }
            catch { return _dtb; }
        }

        /// <summary>
        /// Select data by object from table in database
        /// </summary>
        /// <param name="obj">Condition</param>
        /// <returns>Data</returns>
        public DataTable Select(object obj)
        {
            throw new NotImplementedException();
        }

        /// <summary>
        /// Get a object by key
        /// </summary>
        /// <param name="key">Key need to get</param>
        /// <returns>Object</returns>
        public object GetByKey(object key)
        {
            throw new NotImplementedException();
        }

        /// <summary>
        /// Delete data by id
        /// </summary>
        /// <param name="id">Id</param>
        /// <returns>True is successfull else false</returns>
        public UTL.Session.PerState Delete(long id)
        {
            throw new NotImplementedException();
        }

        /// <summary>
        /// Insert object to table in database
        /// </summary>
        /// <param name="obj">Object need to insert</param>
        /// <returns>True is successfull else false</returns>
        public UTL.Session.PerState Insert(object obj)
        {
            long id = Convert.ToInt64(obj);

            try
            {
                var res = from s in _mdb.tb_Leases
                          where s.Id == id
                          select new
                          {
                              s.RoomId,
                              s.ComingDate,
                              s.LeaveDate,
                              ElectricFee = (s.NewElec - s.OldElec) * s.PriceElec,
                              WaterFee = (s.NewWater - s.OldWater) * s.PriceWater
                          };

                var o = res.Single();

                var lea = new DAL.tb_Receipt()
                {
                    LeaseId = id,
                    RoomFee = 0,
                    WaterFee = o.WaterFee.Value,
                    ElectricFee = o.ElectricFee.Value,
                    OtherFee = 0,
                    Note = null,
                    Debt = 0,
                    Date = DAL.BaseDAL.CurrentTime.Value,
                    Status = 1
                };

                _mdb.tb_Receipts.InsertOnSubmit(lea);

                var x = _mdb.tb_Rooms.Single(k => k.Id == o.RoomId);
                x.StatusId = 1; // empty

                _mdb.SubmitChanges();
                return UTL.Session.PerState.Success;
            }
            catch { return UTL.Session.PerState.Uninsert; }
        }

        /// <summary>
        /// Update object to table in database
        /// </summary>
        /// <param name="obj">Object need to update</param>
        /// <returns>True is successfull else false</returns>
        public UTL.Session.PerState Update(object obj)
        {
            throw new NotImplementedException();
        }
        #endregion

        #region Events
        #endregion

        #region Methods
        public DataTable Select(DateTime fr, DateTime to)
        {
            try
            {
                var res = from s in _mdb.tb_Receipts
                          where s.Date >= fr && s.Date <= to
                          orderby s.Id descending
                          select new
                          {
                              s.Id,
                              s.LeaseId,
                              s.Date,
                              s.RoomFee,
                              s.ElectricFee,
                              s.WaterFee,
                              s.OtherFee,
                              s.Debt,
                              s.Note,
                              s.Status,

                              GuestName = s.tb_Lease.tb_Guest.Name,
                              IdCard = s.tb_Lease.tb_Guest.IdCard,
                              UserName = s.tb_Lease.tb_User.Name,

                              StatusName = s.tb_ReceiptStatus.Name,
                              SumFee = s.RoomFee + s.ElectricFee + s.WaterFee + s.OtherFee + s.Debt
                          };
                return res.ToDataTable();
            }
            catch { return _dtb; }
        }
        #endregion

        #region Overrides
        #endregion

        #region Statics
        #endregion

        #region More
        #endregion
    }
}